#!/usr/bin/python

"""
    File:       db.py
    
    Class:      CMPS 140 Winter 2011
    Project:    Papa Bot
    Date:       03/10/11
    Authors:    Jason Erickson
                Morgan Davis
                DJ Visbal
                Constantine Perepelitsa
                Jae Kim
                Huu Huynh
"""

import sqlite3

class DB(object):

    def __init__(self,debug_mode=False):
        self.debug_mode = debug_mode
        self.database = sqlite3.connect('db/papa-bot.db')
        self.cursor = self.database.cursor()

    # returns a list of tuples (drinkID,name)
    def getAllDrinks(self):
        """Returns a list of tuples (drinkID, name)."""
        self.cursor.execute('''SELECT drinkID, name
            FROM drinks;''')
        return self.cursor.fetchall()

    # returns a list of tuples (ingredientID,name)
    def getAllIngredients(self):
        """Returns a list of tuples (ingredientID, name)."""
        self.cursor.execute('''SELECT ingredientID, name
            FROM ingredients;''')
        return self.cursor.fetchall()

    #Gets all drink IDs
    def getAllDrinkIDs(self):
        """Returns a list of drink IDs."""
        self.cursor.execute('''SELECT drinkID
            FROM drinks''')
        return [c[0] for c in self.cursor.fetchall()]

    #Takes a string name and returns drinkID
    def getDrinkID(self,name):
        """Returns drink ID for given drink name."""
        drinkname = (name,)
        self.cursor.execute('''SELECT drinkID
            FROM drinks
            WHERE name LIKE ?''', drinkname)
        id = self.cursor.fetchall()
        if len(id) == 0: return None
        return id[0][0]

    #Takes drinkID number and returns name of drink
    def getDrinkName(self,drinkID):
        """Returns drink name for given drink ID."""
        id = (drinkID,)
        self.cursor.execute('''SELECT name
            FROM drinks
            WHERE drinkID = ?''', id)
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]

    #Takes drinkID number and returns instructions
    def getInstructions(self,drinkID):
        """Returns instructions for given drink ID."""
        id = (drinkID,)
        self.cursor.execute('''SELECT instructions
            FROM drinks
            WHERE drinkID = ?''', id)
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]

    #Takes drinkID number and returns comment
    def getComment(self,drinkID):
        """Returns comment for given drink ID."""
        id = (drinkID,)
        self.cursor.execute('''SELECT comment
            FROM drinks
            WHERE drinkID = ?''', id)
        result = self.cursor.fetchall()
        # distinguish between None for query failure
        # and None for lack of comment
        if len(result) == 0:
            return "Drink #%d does not exist." % drinkID
        elif result[0][0] == None:
            return None
        return result[0][0]

    #Takes drinkID number and returns rating
    def getRating(self,drinkID):
        """Returns rating for given drink ID."""
        id = (drinkID,)
        self.cursor.execute('''SELECT rating
            FROM drinks
            WHERE drinkID = ?''', id)
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]

    #NOTE: this is NOT the same category as defined in the "categories" table.
    def getCategory(self,drinkID):
        """Returns category of given drink ID."""
        id = (drinkID,)
        self.cursor.execute('''SELECT category
            FROM drinks
            WHERE drinkID = ?''', id)
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]

    def getDrinkAlcohol(self,drinkID):
        """Returns alcoholic content of given drink ID."""
        id = (drinkID,)
        self.cursor.execute('''SELECT alcohol
            FROM drinks
            WHERE drinkID = ?''', id)
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]

    #Takes drinkID number and returns glasstype
    def getGlassType(self,drinkID):
        """Returns glass type for given drink ID."""
        id = (drinkID,)
        self.cursor.execute('''SELECT servein
            FROM drinks
            WHERE drinkID = ?''', id)
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]

    #Get the highest rated drinks
    # NOTE: this gets all drinks with the single highest rating
    def getBestRating(self):
        """Returns a list of tuples (name, rating) with highest ratings."""
        self.cursor.execute('''SELECT name, rating
            FROM drinks 
            WHERE rating IN(
            SELECT MAX(rating)
            FROM drinks)''')
        return self.cursor.fetchall()

    #Get highest rated drinks except drinks user doesn't like
    def getPopularDrinks(self, userID):
        """Returns a list of drinks"""
        id = (userID,)
        self.cursor.execute('''SELECT name
            FROM drinks
            WHERE rating IN(SELECT MAX(rating) FROM drinks)
            EXCEPT
            SELECT name
            FROM drinks
            WHERE drinkID IN (
                SELECT drinkID
                FROM user_preferences
                WHERE rating = 0
                AND userID = ?)''', id)
        result = [drink[0] for drink in self.cursor.fetchall()]
        return result
        
    #get a list of popular drink IDs
    def getPopularDrinkIDs(self, userID):
        """Returns a list of popular drinks IDs"""
        id = (userID,)
        self.cursor.execute('''SELECT drinkid
            FROM drinks
            WHERE rating IN(SELECT MAX(rating) FROM drinks)''')
        result = [drink[0] for drink in self.cursor.fetchall()]
        return result
        
    #get a list of drink IDs the user doesn't like
    def getUserDislikedDrinkIDs(self,userID):
        """Returns a list of drinkIDs the user doesn't like"""
        id = (userID,)
        self.cursor.execute('''SELECT drinkid
            FROM drinks
            WHERE drinkID IN (
                SELECT drinkID
                FROM user_preferences
                WHERE rating = 0
                AND userID = ?)''', id)
        result = [drink[0] for drink in self.cursor.fetchall()]
        return result

    #Takes drinkID number and returns the ingredients
    def getIngredients(self,drinkID):
        """Returns a list of ingredients for given drink ID."""
        id = (drinkID,)
        self.cursor.execute('''SELECT ingredientID, name
            FROM ingredients
            WHERE ingredientID IN(
            SELECT ingredientID
            FROM drink_ingredients
            WHERE drinkID = ?)''', id)
        ingredient_list = [ingredient[1] for ingredient in self.cursor.fetchall()]
        return ingredient_list

    #Takes drinkID number and returns all ingredient IDs
    def getIngredientIDs(self,drinkID):
        """Returns a list of ingredient ID's for given drink ID."""
        id = (drinkID,)
        self.cursor.execute('''SELECT ingredientID
            FROM drink_ingredients
            WHERE drinkID = ?''', id)
        return [c[0] for c in self.cursor.fetchall()]

    #Takes drinkID number and returns ingredients
    def getRelatedIngredients(self,drinkID):
        """Returns a list of related ingredients for given drink ID."""
        id = (drinkID,)
        self.cursor.execute('''SELECT name
            FROM ingredients
            WHERE ingredientID IN(
                SELECT relatedIngredientID
                FROM related_ingredients
                WHERE ingredientID IN(
                    SELECT ingredientID
                    FROM drink_ingredients
                    WHERE drinkID = ?))''', id)
        return [c[0] for c in self.cursor.fetchall()]

    #Takes an ingredientID and returns the name
    def getIngredientName(self,ingredientID):
        """Returns the ingredient name for a given ingredient ID."""
        self.cursor.execute('''SELECT name
            FROM ingredients
            WHERE ingredientID = ?''', (ingredientID,))
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]

    #Takes an ingredient name and returns the id
    def getIngredientID(self,name):
        """Returns the ingredient ID for given ingredient name."""
        ingredientname = (name,)
        self.cursor.execute('''SELECT ingredientID
            FROM ingredients
            WHERE name LIKE ?''', ingredientname)
            #WHERE name = ?''', ingredientname)
        id = self.cursor.fetchall()
        if len(id) == 0: return None
        return id[0][0]

    #returns Description of ingredient
    def getIngredientDescription(self,ingredientID):
        """Returns the ingredient description for the given ingredient ID."""
        id = (ingredientID,)
        self.cursor.execute('''SELECT description
            FROM ingredients
            WHERE ingredientID = ?''', id)
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]

    #returns category of ingredient
    def getIngredientCategory(self,ingredientID):
        """Returns ingredient category for the given ingredient ID."""
        id = (ingredientID,)
        self.cursor.execute('''SELECT category
            FROM ingredients
            WHERE ingredientID = ?''', id)
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]

    #returns alcohol contents of ingredient
    def getIngredientAlcohol(self,ingredientID):
        """Returns the alcohol content of the given ingredient ID."""
        id = (ingredientID,)
        self.cursor.execute('''SELECT alcohol
            FROM ingredients
            WHERE ingredientID = ?''', id)
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]
        
    def getDrinkIngredientsPositionsAmounts(self,drinkID):
        '''Return a list of (ingredientID,position,amount)'''
        id = (drinkID,)
        self.cursor.execute('''SELECT ingredientID, position, amount
            FROM drink_ingredients
            WHERE drinkID = ?''', id)
        return self.cursor.fetchall()

    def getIngredientAmount(self,drinkID):
        """Returns a list of ingredient amounts as strings for given drink ID."""
        id = (drinkID,)
        self.cursor.execute('''SELECT ingredientID, amount, detail
            FROM drink_ingredients
            WHERE drinkID = ?''', id)
        result = self.cursor.fetchall()
        #return ['amount' + 'ingredient',...]
        return [(r[1] or '') + r[2] for r in result]

    #returns drink names that contain the inputted ingredient
    def getRelatedDrinks(self,ingredientID):
        """Returns a list of drink names that contain the given ingredient ID."""
        id = (ingredientID,)
        self.cursor.execute('''SELECT DISTINCT d.name
            FROM Drink_Ingredients di
            WHERE di.ingredientID = ?''', id)
        return [c[0] for c in self.cursor.fetchall()]
        
        # WHERE d.drinkid = di.drinkid
        # AND i.ingredientID = di.ingredientID
        # AND i.ingredientID = ?''', id)
        
    #makes sense...
    def getDrinkNamesContaining(self,ingredientID):
        return self.getRelatedDrinks(ingredientID)
    
    # revision by morgan 3-14
    def getRelatedDrinkIDs(self,ingredientID):
        """Returns a list of drink ID's that contain the given ingredient ID."""
        id = (ingredientID,)
        self.cursor.execute('''SELECT DISTINCT di.drinkid
            FROM Drink_Ingredients di
            WHERE di.ingredientID = ?''', id)
        return [c[0] for c in self.cursor.fetchall()]
    
    #makes sense...
    def getDrinkIDsContaining(self,ingredientID):
        return self.getRelatedDrinkIDs(ingredientID)
        
    # gets (name,rating) tubes for all drinks containing the ingredient id given
    def getDrinkNamesAndRatingsContaining(self,ingredientID):
        id = (ingredientID,)
        self.cursor.execute('''SELECT DISTINCT d.name, d.rating
            FROM drinks d, drink_ingredients di
            WHERE d.drinkid = di.drinkid
            AND di.ingredientid = ?''', id)
        return self.cursor.fetchall()
    
    # #gets (drinkID,rating) tuples from drink names
    # def getDrinkIDAndRating(self,name):
    #     lname = (name,)
    #     self.cursor.execute('''SELECT drinkID, rating
    #         FROM drinks
    #         WHERE name = ?''', lname)
    #     return self.cursor.fetchall()

    #gets a list of category names and ids
    def getAllCategories(self):
        """Returns a list of tuples (categoryID, name)."""
        self.cursor.execute('''SELECT categoryID, name
            FROM categories''')
        return self.cursor.fetchall()

    def getCategoryID(self,cname):
        """Returns the category ID for the given category name."""
        self.cursor.execute('''SELECT categoryID
            FROM categories
            WHERE name = ?;''', (cname,))
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]

    def getCategoryName(self,cid):
        """Returns the category name of the given category ID."""
        self.cursor.execute('''SELECT name
            FROM categories
            WHERE categoryID = ?;''', (cid,))
        result = self.cursor.fetchall()
        if len(result) == 0: return None
        return result[0][0]

    def getDrinksInCategory(self,categoryID):
        """Returns a list of drink names in the given category ID."""
        self.cursor.execute('''SELECT DISTINCT d.name
            FROM drinks d, drink_categories c
            WHERE d.drinkID = c.drinkID
            AND c.categoryID = ?''', (categoryID,))
        return [c[0] for c in self.cursor.fetchall()]
        
    def getDrinkIDsInCategory(self,categoryID):
        """Returns a list of drink ids in the given category ID."""
        self.cursor.execute('''SELECT DISTINCT d.drinkid
            FROM drinks d, drink_categories c
            WHERE d.drinkID = c.drinkID
            AND c.categoryID = ?''', (categoryID,))
        return [c[0] for c in self.cursor.fetchall()]
        
    def getAllIngredientIDsAndAlcoholContents(self):
        """Returns a list of ingredient IDs their alcohol contents (STRING)."""
        self.cursor.execute('''SELECT ingredientID, alcohol
            FROM ingredients''')
        return self.cursor.fetchall()
        
    # returns a list of tuples (userID,name)
    def getAllUsers(self):
        """Returns a list of tuples (userID, name)."""
        self.cursor.execute('''SELECT userID, name
            FROM users;''')
        return self.cursor.fetchall()

    #inserts new user name and id into user table and returns the new user id
    def createUser(self,uname):
        """Creates a new user with the given name and returns the user ID."""
        name = (uname,)
        self.cursor.execute('''INSERT INTO users(name)
            VALUES(?)''', (name))
        self.database.commit()
        return self.getUserID(uname)

    #retrieves userID based on name
    def getUserID(self,uname):
        """Returns the user ID for the given user name."""
        name = (uname,)
        self.cursor.execute('''SELECT userID
            FROM users
            WHERE name = ?''', name)
        all = self.cursor.fetchall()
        if len(all) == 0: return None
        return all[0][0]

    def addToUserPreferences(self,userid,drinkid,rating):
        """Given user ID, drink ID, and rating, update user preferences."""
        self.cursor.execute('''INSERT INTO user_preferences
                          VALUES(?,?,?)''', (userid, drinkid, rating))
        self.database.commit()

    # gets all drinks + ratings for a user
    def getUserPreferences(self,userid):
        """Returns a dictionary { drinkID => rating } for the given user ID."""
        self.cursor.execute('''SELECT drinkID,rating
            FROM user_preferences
            WHERE userID = ?;''', (userid,))
        return dict(self.cursor.fetchall())

    def getUserLikedDrinks(self,userid):
        """Returns a list of drink ID's with a positive rating for the given user ID."""
        self.cursor.execute('''SELECT drinkID,rating
            FROM user_preferences
            WHERE userID = ?;''', (userid,))
        rated = self.cursor.fetchall()
        return [i[0] for i in rated if i[1] == True] # return only drinks user likes

    #retrieves user name based on userID
    def getUserName(self,uid):
        """Return user name of the given user ID."""
        userid = (uid,)
        self.cursor.execute('''SELECT name
            FROM users
            WHERE userID = ?''', userid)
        result = self.cursor.fetchone()
        if len(result) == 0: return None
        return result[0]

    #deletes a user from users table
    def deleteUser(self,id):
        """Deletes a user from the database."""
        userid = (id,)
        self.cursor.execute('''DELETE FROM users WHERE userID = ?''', userid)
        self.cursor.execute('''DELETE FROM cabinets WHERE userID = ?''', userid)
        self.database.commit()

    #add ingredients to cabinets, takes userid and ingredientid
    def addToCabinet(self,userid,ingredientid):
        """Given user ID and ingredient ID, adds the ingredient to the user's cabinet."""
        self.cursor.execute('''INSERT INTO cabinets
            VALUES(?,?)''', (userid, ingredientid))
        self.database.commit()

    #delete ingredients off of cabinet
    def removeFromCabinet(self,userid,ingredientid):
        """Given user ID and ingredient ID, deletes the ingredient from the user's cabinet."""
        self.cursor.execute('''DELETE FROM cabinets
            WHERE userID = ? AND ingredientID = ?''',
                       (userid, ingredientid))
        self.database.commit()

    #get contents of liquor cabinet
    def getCabinet(self,uid):
        """Returns a list of ingredient ID's for the ingredients in the given user ID's cabinet."""
        userid = (uid,)
        self.cursor.execute('''SELECT ingredientID
            FROM cabinets
            WHERE userID = ?''', userid)
        return [c[0] for c in self.cursor.fetchall()]

